<?php

class bbcoin_user_money_widget
{
    private $col_arr = array(
        'A',
        'B',
        'C',
        'D',
        'E',
        'F',
        'G',
        'H',
        'I',
        'J',
        'K',
        'L',
        'M',
        'N',
        'O',
        'P',
        'Q',
        'R',
        'S',
        'T',
        'U',
        'V',
        'W',
        'X',
        'Y',
        'Z',
        'AA',
        'AB',
        'AC',
        'AD',
        'AE',
        'AF',
        'AG',
        'AH',
        'AI',
        'AJ',
        'AK',
        'AL',
        'AM',
        'AN'
    );

    public function __construct() {
        $this->dir = EXPORT_PATH;
    }

    private $dir = '';
    private $path = '';
    private $time_to_run = '';

    /*
     * 生成合伙人剩余抱抱币和余额的excel
     * */
    public function generate_excel($is_export = 0) {
        $file_name = Request::getParam('file_name', 'bbcoin_user_monry_' . date('Y-m-d') . '_');
        $worksheet_list = array(
            '合伙人抱抱币、余额',
            '终端对应抱抱币、余额',
        );
        $title_list = array(
            //合伙人抱抱币、余额
            array(
                array(
                    'property' => array(
                        'font_size' => 11,
                        'font_weight' => 'bold',
                    ),
                    'id' => array('title' => '合伙人ID'),
                    'user_name' => array('title' => '登录账号'),
                    'realname' => array('title' => '用户名'),
                    'bbcoin' => array('title' => '抱抱币余额'),
                    'user_money' => array('title' => '余额'),
                )
            ),
            //终端对应抱抱币、余额
            array(
                array(
                    'property' => array(
                        'font_size' => 11,
                        'font_weight' => 'bold',
                    ),
                    'user_id' => array('title' => '用户ID'),
                    'realname' => array('title' => '用户名'),
                    'store_id' => array('title' => '影院/终端ID'),
                    'user_name' => array('title' => '影院/终端APP账号'),
                    'agency_id' => array('title' => '所属合伙人ID'),
                    'agency_name' => array('title' => '所属合伙人'),
                    'title' => array('title' => '影院/终端名称'),
                    'title_branch' => array('title' => '影院/终端二级名称'),
                    'bbcoin' => array('title' => '抱抱币余额'),
                    'user_money' => array('title' => '余额'),
                )
            ),
        );
        // 数据列表
        $data_list = array(
            array(
                //合伙人抱抱币、余额
                'data' => $this->get_user_bbcoin_user_money_list(),
            ),
            array(
                //终端对应抱抱币、余额
                'data' => $this->get_store_bbcoin_user_money(),
            ),
        );

        // 执行导出execl
        return $this->exec_export($file_name, $worksheet_list, $title_list, $data_list, $is_export);
    }

    private function get_store_bbcoin_user_money() {
        $SQL = 'SELECT ' .
               join(',', array(
                   'u.`bbcoin`',
                   'u.`user_money`',
                   'u.`realname`',
                   'u.`user_name`',
                   'u2.`id` \'agency_id\'',
                   'u2.`realname` \'agency_name\'',
                   'u.`id` \'user_id\'',
                   's.`id` \'store_id\'',
                   's.`title`',
                   's.`title_branch`',
               )) .
               ' FROM `store` s' .
               ' LEFT JOIN `user` u ON u.`id`=s.`user_id`' .
               ' LEFT JOIN `user` u2 ON u2.`id`=s.`agency_id`' .
               ' WHERE s.`agency_id`>0' .
               ' AND u.`is_enable`=1' .
               ' AND s.`is_enable`=1';

        $list = run_sql('store', 'getAll', $SQL);

        return $list;
    }

    private function get_user_bbcoin_user_money_list() {
        // 获取所有有效的经销商
        $cur_dealer_info = user_helper::get_all_dealers_info(array(
            'is_enable' => 1,
            'status' => 1,
        ));

        return $cur_dealer_info;
    }

    /**
     * 执行导出execl
     * @param $file_name 文件名
     * @param $export_list 数据列表
     */
    private function exec_export($file_name, $worksheet_list, $title_list, $data_list, $is_export) {
        if (!$file_name || !$worksheet_list || !$title_list || !$data_list) {
            return false;
        }

        //初始化excel对象
        $objPHPExcel = new PHPExcel();
        $objWriter = new PHPExcel_Writer_Excel5($objPHPExcel);

        $objPHPExcel->getDefaultStyle()->getFont()->setName('宋体');
        // 建立循环索引
        $k = 0;
        foreach ($worksheet_list as $worksheet) {

            if ($k) {
                //添加一个新的worksheet
                $objPHPExcel->createSheet();
            }
            // 设置工作表名称
            $objPHPExcel->getSheet($k)->setTitle($worksheet);
            $start_row = 1;
            // 统计需要多少列
            $col_num = 0;
            $col_title_alias_arr = array();
            $col_title_arr = array();
            if (count($title_list[$k]) > 1) {
                foreach ($title_list[$k] as $kk => $v) {
                    if ($kk == 'property') {
                        continue;
                    }

                    $col_num += count($title_list[$k][$kk]['content']) - 1;

                    array_push($col_title_alias_arr, array_slice(array_keys($v['content']), 1));
                    foreach ($v['content'] as $kkk => $vv) {
                        if ($kkk == 'property') {
                            continue;
                        }
                        array_push($col_title_arr, $vv['title']);
                    }
                }
                $start_row = 2;

            } else {
                $col_num = count($title_list[$k][0]) - 1;
                $col_title_alias_arr = array_slice(array_keys($title_list[$k][0]), 1);
                foreach ($title_list[$k][0] as $kkk => $vv) {
                    if ($kkk == 'property') {
                        continue;
                    }
                    array_push($col_title_arr, $vv['title']);
                }
                $start_row = 1;
            }
            $col_arr = array_slice($this->col_arr, 0, $col_num);

            // 设置单元格属性
            if ($start_row == 2 && 0 == $k) {
                // 合并单元格
                $objPHPExcel->getSheet($k)->mergeCells("A1:M1");
                $objPHPExcel->getSheet($k)->mergeCells("N1:Y1");

                // 设置单元格的高度
                $objPHPExcel->getSheet($k)->getRowDimension(1)->setRowHeight(35);
                //设置单位格内容
                $objPHPExcel->getSheet($k)->setCellValue('A1', $title_list[0]['dealer']['title']);
                $objPHPExcel->getSheet($k)->setCellValue('N1', $title_list[0]['store']['title']);
                //格式化数字为字符串，避免导致数字默认右对齐单元格
                // $objPHPExcel->getSheet($k)->getStyle('A1')->getNumberFormat()->setFormatCode("@");
                // $objPHPExcel->getSheet($k)->getStyle('M1')->getNumberFormat()->setFormatCode("@");
                //设置自动换行
                $objPHPExcel->getSheet($k)->getStyle('A1')->getAlignment()->setWrapText(true);
                $objPHPExcel->getSheet($k)->getStyle('N1')->getAlignment()->setWrapText(true);
                // 标题字体加粗
                $objPHPExcel->getSheet($k)->getStyle('A1')->getFont()->setBold(true);
                $objPHPExcel->getSheet($k)->getStyle('N1')->getFont()->setBold(true);
                // 设置字体大小
                $objPHPExcel->getSheet($k)->getStyle('A1')->getFont()->setSize(20);
                $objPHPExcel->getSheet($k)->getStyle('N1')->getFont()->setSize(20);
                // 设置字体居中
                //设置对齐方式
                $objPHPExcel->getSheet($k)->getStyle('A1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
                $objPHPExcel->getSheet($k)->getStyle('N1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
                $objPHPExcel->getSheet($k)->getStyle('A1')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
                $objPHPExcel->getSheet($k)->getStyle('N1')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);

                // 设置填充颜色
                //设置填充颜色
                $objPHPExcel->getSheet($k)->getStyle('N1')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
                $objPHPExcel->getSheet($k)->getStyle('N1')->getFill()->getStartColor()->setARGB('FF00B0F0');
                $objPHPExcel->getSheet($k)->getStyle('A1')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
                $objPHPExcel->getSheet($k)->getStyle('A1')->getFill()->getStartColor()->setARGB('FF92D050');

            } elseif (3 == $k) {
                // 合并单元格
                $objPHPExcel->getSheet($k)->mergeCells("A1:F1");
                $objPHPExcel->getSheet($k)->mergeCells("G1:L1");

                // 设置单元格的高度
                $objPHPExcel->getSheet($k)->getRowDimension(1)->setRowHeight(35);
                //设置单位格内容
                $objPHPExcel->getSheet($k)->setCellValue('A1', $title_list[3]['machine']['title']);
                $objPHPExcel->getSheet($k)->setCellValue('G1', $title_list[3]['adbox']['title']);
                //格式化数字为字符串，避免导致数字默认右对齐单元格
                // $objPHPExcel->getSheet($k)->getStyle('A1')->getNumberFormat()->setFormatCode("@");
                // $objPHPExcel->getSheet($k)->getStyle('L1')->getNumberFormat()->setFormatCode("@");
                //设置自动换行
                $objPHPExcel->getSheet($k)->getStyle('A1')->getAlignment()->setWrapText(true);
                $objPHPExcel->getSheet($k)->getStyle('G1')->getAlignment()->setWrapText(true);
                // 标题字体加粗
                $objPHPExcel->getSheet($k)->getStyle('A1')->getFont()->setBold(true);
                $objPHPExcel->getSheet($k)->getStyle('G1')->getFont()->setBold(true);
                // 设置字体大小
                $objPHPExcel->getSheet($k)->getStyle('A1')->getFont()->setSize(20);
                $objPHPExcel->getSheet($k)->getStyle('G1')->getFont()->setSize(20);
                // 设置字体居中
                //设置对齐方式
                $objPHPExcel->getSheet($k)->getStyle('A1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
                $objPHPExcel->getSheet($k)->getStyle('G1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
                $objPHPExcel->getSheet($k)->getStyle('A1')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
                $objPHPExcel->getSheet($k)->getStyle('G1')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);

                // 设置填充颜色
                //设置填充颜色
                $objPHPExcel->getSheet($k)->getStyle('G1')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
                $objPHPExcel->getSheet($k)->getStyle('G1')->getFill()->getStartColor()->setARGB('FF00B050');
                $objPHPExcel->getSheet($k)->getStyle('A1')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
                $objPHPExcel->getSheet($k)->getStyle('A1')->getFill()->getStartColor()->setARGB('FF00B0F0');
            }

            $i = 0;
            // 设置文档属性
            foreach ($col_arr as $key => $val) {
                //设置列的宽度
                if (0 == $k) {
                    if (in_array($val, array('T', 'H', 'I', 'J', 'K', 'M', 'Y'))) {
                        $objPHPExcel->getSheet($k)->getColumnDimension($val)->setWidth(15);
                    } elseif (in_array($val, array('B', 'M', 'N'))) {
                        $objPHPExcel->getSheet($k)->getColumnDimension($val)->setWidth(20);
                    } elseif (in_array($val, array('A', 'G'))) {
                        $objPHPExcel->getSheet($k)->getColumnDimension($val)->setWidth(12);
                    } else {
                        $objPHPExcel->getSheet($k)->getColumnDimension($val)->setWidth(10);
                    }
                } elseif (1 == $k) {
                    if (in_array($val, array('A', 'C', 'D'))) {
                        $objPHPExcel->getSheet($k)->getColumnDimension($val)->setWidth(20);
                    } elseif (in_array($val, array('E', 'F', 'H', 'J'))) {
                        $objPHPExcel->getSheet($k)->getColumnDimension($val)->setWidth(12);
                    } else {
                        $objPHPExcel->getSheet($k)->getColumnDimension($val)->setWidth(10);
                    }
                } elseif (2 == $k) {
                    if (in_array($val, array('B', 'C', 'D', 'F'))) {
                        $objPHPExcel->getSheet($k)->getColumnDimension($val)->setWidth(22);
                    } elseif (in_array($val, array('A'))) {
                        $objPHPExcel->getSheet($k)->getColumnDimension($val)->setWidth(8);
                    } else {
                        $objPHPExcel->getSheet($k)->getColumnDimension($val)->setWidth(12);
                    }
                } else {
                    if (in_array($val, array('C', 'E', 'I', 'K'))) {
                        $objPHPExcel->getSheet($k)->getColumnDimension($val)->setWidth(10);
                    } elseif (in_array($val, array('A', 'G'))) {
                        $objPHPExcel->getSheet($k)->getColumnDimension($val)->setWidth(15);
                    } else {
                        $objPHPExcel->getSheet($k)->getColumnDimension($val)->setWidth(22);
                    }
                }

                // $objPHPExcel->getSheet($k)->getColumnDimension($val)->setWidth(12);
                //自动设置单元格宽度   例：
                // $objPHPExcel->getSheet($k)->getColumnDimension($val)->setAutoSize(true);
                //设置单位格内容
                $objPHPExcel->getSheet($k)->setCellValue($val . $start_row, $col_title_arr[$key]);
                //格式化数字为字符串，避免导致数字默认右对齐单元格
                $objPHPExcel->getSheet($k)->getStyle($val)->getNumberFormat()->setFormatCode("@");
                //设置自动换行
                $objPHPExcel->getSheet($k)->getStyle($val)->getAlignment()->setWrapText(true);
                // 标题字体加粗
                $objPHPExcel->getSheet($k)->getStyle($val . $start_row)->getFont()->setBold(true);
                //设置对齐方式
                $objPHPExcel->getSheet($k)->getStyle($val)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
                $objPHPExcel->getSheet($k)->getStyle($val .
                                                     $start_row)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
                $objPHPExcel->getSheet($k)->getStyle($val)->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
                $objPHPExcel->getSheet($k)->getStyle($val .
                                                     $start_row)->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);

                // 设置边框
                if ($k == 0) {
                    $objPHPExcel->getSheet($k)->getStyle($val .
                                                         $start_row)->getBorders()->getTop()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
                    $objPHPExcel->getSheet($k)->getStyle($val .
                                                         $start_row)->getBorders()->getBottom()->setBorderStyle(PHPExcel_Style_Border::BORDER_THICK);
                    $objPHPExcel->getSheet($k)->getStyle($val .
                                                         $start_row)->getBorders()->getLeft()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
                    $objPHPExcel->getSheet($k)->getStyle($val .
                                                         $start_row)->getBorders()->getRight()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
                }

                $i++;
                if ($start_row == 2) {
                    // 设置背景色
                    //设置填充颜色
                    //                     if ($i > 9 && 0 == $k) {
                    //                         $objPHPExcel->getSheet($k)->getStyle($val.$start_row)->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
                    //                         $objPHPExcel->getSheet($k)->getStyle($val.$start_row)->getFill()->getStartColor()->setARGB('FF92D050');
                    //                     }

                    // 设置完成后冻结行(冻结前两行则设置为A3(只冻结行))
                    $objPHPExcel->getSheet($k)->freezePane('A3');
                } else {
                    // 必须在设置完值之后才可以
                    $objPHPExcel->getSheet($k)->freezePane('A2');
                }
            }
            $start_row++;

            if (isset($col_title_alias_arr[1]) && is_array($col_title_alias_arr[1])) {
                $col_title_alias_arr = array_merge($col_title_alias_arr[0], $col_title_alias_arr[1]);
            }
            foreach ($data_list[$k]['data'] as $data_key => $val) {
                foreach ($col_arr as $col_key => $col) {
                    $place = $col . $start_row;
                    $col_content = $col_title_arr[$col_key];

                    $value = '';

                    $val_keys = @array_keys($val) ? array_keys($val) : array();
                    if (in_array($col_title_alias_arr[$col_key], $val_keys)) {
                        $value = $val[$col_title_alias_arr[$col_key]];
                    }

                    $objPHPExcel->getSheet($k)->setCellValue($place, $value);

                }
                $start_row++;
            }

            $k++;
        }

        //设置当前的sheet索引
        $objPHPExcel->setActiveSheetIndex(0);

        $title = trim($file_name) . date('YmdHis') . ".xlsx";

        if ($is_export) {
            //设置页面类型
            header("Content-Type: application/vnd.ms-excel");
            //用于下载的文件头
            header("Content-Type: application/force-download");
            header("Content-Type: application/download");
            header("Content-Type: application/octet-stream");

            // ff正常，ie不正常，下面是从网站找到的解决方案
            if (preg_match('/MSIE/', $_SERVER['HTTP_USER_AGENT'])) {
                $title = rawurlencode($title);
            }
            header("Content-Disposition: attachment; filename=" . $title);
            header("Content-Transfer-Encoding: binary");
            //最后修改时间
            header("Last-Modified: " . gmdate("D, d M Y H:i:s") . " GMT");
            //不使用缓存
            header('Cache-Control: ust-revalidate, post-check=0, pre-check=0');
            header('Cache-Control: public');
            header("Expires: 0");
            header("Pragma: public");
            $objWriter->save('php://output');
            exit;
        } else {
            $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
            $this->path = $this->dir . $title;
            $objWriter->save($this->path);
        }
        //         $objWriter->save('/data/svn_data/outsourcing2014/web/baobaotang/web/excel/'.$title);
    }

    /**
     * 导出excel发送邮件
     */
    public function export_list_email($is_export = 0) {
        // 导出
        $this->generate_excel($is_export);
        $url = STATIC_URL . '/images/data/' . basename($this->path);
        // 给客户发一封

        _widget('email')->mail(email_config::$send_email_list, '抱抱堂抱抱币余额相关数据', '下载地址： ' . $url);

    }
}

?>